clear all
set more off

use "$inputs/prowess/Identity.dta"
keep co_code1 co_nic_code17 nic_prod_code18
generate nic_08_4dig = substr(nic_prod_code18,1,4)
generate nic_08_2dig  = substr(nic_prod_code18,1,2)
save "$working/nic_code.dta", replace

/* List of variables I was using for my JMP:
sa_rawmat_stores_spares, sa_gross_fixed_assets, sa_compensation_to_employees, sa_export_goods, sa_rnd_exp, sa_sales */

* This sections prepares intermediary files for matching financial data and needs to be run only once
use "$inputs/prowess/Standalone Annual Financial Statements_1_500.dta", clear
keep sa_finance1_cocode sa_finance1_year sa_ann_rep_months sa_sales sa_rnd_exp sa_compensation_to_e_160 sa_rawmat_stores_spa_146
gen row_id = _n
save "$working/sa1", replace

use "$inputs/prowess/Standalone Annual Financial Statements_1501_2000.dta", clear
keep sa_finance1_cocode sa_gross_fixed_asset_1562
gen row_id = _n
save "$working/sa2", replace

use "$inputs/prowess/Standalone Annual Financial Statements_2001_2500.dta", clear
keep sa_finance1_cocode sa_total_assets sa_gross_fixed_asset_2206 sa_intangible_ast sa_net_goodwill sa_goodwill sa_goodwill_addn sa_goodwill_addn_rev_2211 sa_goodwill_deduct sa_goodwill_cumm_dep_2213 sa_goodwill_dep sa_net_sw sa_software sa_sw_addn sa_sw_addn_reval sa_sw_deduct sa_sw_cumm_dep sa_sw_dep sa_net_mining_rights_2222 sa_mining_rights_ass_2223 sa_mining_rights_add_2224 sa_mining_rights_add_2225 sa_mining_rights_del_2226 sa_mining_rights_cum_2227 sa_mining_rights_dep_2228 sa_net_oth_intangibl_2229 sa_oth_intangible_as_2230 sa_oth_intangible_as_2231 sa_oth_intangible_as_2232 sa_oth_intangible_as_2233 sa_oth_intangible_as_2234 sa_oth_intangible_as_2235 sa_intangible_ast_ad_2236 sa_intangible_ast_ad_2237 sa_intangible_ast_de_2238 sa_intangible_ast_cu_2239 sa_intangible_ast_de_2240 sa_net_intangible_as_2241 sa_land_n_building sa_net_land sa_land sa_land_addn sa_land_addn_reval sa_land_deduct sa_land_cumm_dep sa_land_dep sa_net_freehold_land_2250 sa_net_leasehold_lan_2251 sa_net_leasehold_imp_2252 sa_gross_leasehold_i_2253 sa_leasehold_imprvmn_2254 sa_leasehold_imprvmn_2255 sa_leasehold_imprvmn_2256 sa_leasehold_imprvmn_2257 sa_leasehold_imprvmn_2258 sa_net_building sa_building sa_building_addn sa_building_addn_rev_2262 sa_building_deduct sa_building_cumm_dep_2264 sa_building_dep sa_land_building_add_2266 sa_land_n_building_a_2267 sa_land_n_building_d_2268 sa_land_n_building_c_2269 sa_land_n_building_d_2270 sa_net_land_n_buildi_2271 sa_plant_mach_comput_2272 sa_net_plant sa_plant sa_plant_addn sa_plant_addn_reval sa_plant_deduct sa_plant_cumm_dep sa_plant_dep sa_net_computer_it sa_computer_it sa_computer_it_addn sa_computer_it_addn__2283 sa_computer_it_deduc_2284 sa_computer_it_cumm__2285 sa_computer_it_dep sa_net_elec_install__2287 sa_elec_install_fitt_2288 sa_elec_install_fitt_2289 sa_elec_install_fitt_2290 sa_elec_install_fitt_2291 sa_elec_install_fitt_2292 sa_elec_install_fitt_2293 sa_plant_mach_comput_2294 v295 sa_plant_mach_comput_2296 sa_plant_mach_comput_2297 sa_plant_mach_comput_2298 sa_net_plant_mach_co_2299 sa_transport_comm_eq_2300 sa_net_transport_inf_2301 sa_transport_infra sa_transport_infra_a_2303 sa_transport_infra_a_2304 sa_transport_infra_d_2305 sa_transport_infra_c_2306 sa_transport_infra_d_2307 sa_net_transport_veh_2308 sa_transport_vehicle_2309 sa_transport_vehicle_2310 sa_transport_vehicle_2311 sa_transport_vehicle_2312 sa_transport_vehicle_2313 sa_transport_vehicle_2314 sa_net_comm_equip sa_comm_equip sa_comm_equip_addn sa_comm_equip_addn_r_2318 sa_comm_equip_deduct_2319 sa_comm_equip_cumm_d_2320 sa_comm_equip_dep sa_transport_comm_eq_2322 v323 sa_transport_comm_eq_2324 sa_transport_comm_eq_2325 v326 sa_net_transport_com_2327 sa_furn_social_oth_f_2328 sa_net_furn_and_fixt_2329 sa_furn_and_fixtures_2330 sa_furn_and_fixtures_2331 sa_furn_and_fixtures_2332 sa_furn_and_fixtures_2333 sa_furn_and_fixtures_2334 sa_furn_and_fixtures_2335 sa_net_social_amenit_2336 sa_social_amenities sa_social_amenities__2338 sa_social_amenities__2339 sa_social_amenities__2340 sa_social_amenities__2341 sa_social_amenities__2342 sa_net_oth_fixed_ast_2343 sa_oth_fixed_ast sa_oth_fixed_ast_add_2345 sa_oth_fixed_ast_add_2346 sa_oth_fixed_ast_ded_2347 sa_oth_fixed_ast_cum_2348 sa_oth_fixed_ast_dep_2349 sa_furn_social_oth_f_2350 v351 sa_furn_social_oth_f_2352 sa_furn_social_oth_f_2353 sa_furn_social_oth_f_2354 sa_net_furn_social_o_2355 sa_gross_fixed_ast_a_2356 sa_gross_fixed_ast_a_2357 sa_gross_fixed_ast_d_2358 sa_gross_fixed_ast_c_2359 sa_gross_fixed_ast_d_2360 sa_net_fixed_assets
gen row_id = _n
save "$working/sa2_1", replace

use "$inputs/prowess/Standalone Annual Financial Statements_2501_3000.dta", clear
keep sa_finance1_cocode sa_no_of_employees sa_export_goods
gen row_id = _n
save "$working/sa3", replace


use "$working/sa1", clear
merge 1:1 sa_finance1_cocode row_id using "$working/sa2", nogen assert(3)
merge 1:1 sa_finance1_cocode row_id using "$working/sa2_1", nogen assert(3)
merge 1:1 sa_finance1_cocode row_id using "$working/sa3", nogen assert(3)

rename sa_finance1_cocode products_cocode1
rename sa_finance1_year prod_date3
drop row_id sa_ann_rep_months

merge 1:1 products_cocode1 prod_date3 using "$working/en"
rename _merge merged_energy_firm
la var merged_energy_firm "results for merging firm level energy"

merge 1:1 products_cocode1 prod_date3 using "$working/rawmat"
rename _merge merged_rawmat
la var merged_rawmat "results for merging firm level rawmat"


merge 1:m products_cocode1 prod_date3 using "$working/product_energy_cleaned.dta", keep(3) nogen
rename products_cocode1 co_code1
merge m:1 co_code1 using "$working/nic_code.dta", keep(3) nogen
rename co_code1 products_cocode1
order products_cocode1 prod_date3 ann_rep_months4 nic_08_2dig nic_08_4dig product_name5 product_unit sales_unit 
destring sa_sales sa_rawmat_stores_spa_146 sa_compensation_to_e_160 sa_rnd_exp sa_gross_fixed_asset_1562 sa_export_goods sa_no_of_employees, replace force
isid products_cocode1 prod_date3 product_name5

tostring(prod_date3), replace
gen year = substr(prod_date3, 1, 4)
destring year, replace force

***Destringing
foreach variable in sa_total_assets sa_gross_fixed_asset_2206 sa_intangible_ast sa_net_goodwill sa_goodwill sa_goodwill_addn sa_goodwill_addn_rev_2211 sa_goodwill_deduct ///
sa_goodwill_cumm_dep_2213 sa_goodwill_dep sa_net_sw sa_software sa_sw_addn sa_sw_addn_reval sa_sw_deduct sa_sw_cumm_dep sa_sw_dep sa_net_mining_rights_2222 sa_mining_rights_ass_2223 ///
 sa_mining_rights_add_2224 sa_mining_rights_add_2225 sa_mining_rights_del_2226 sa_mining_rights_cum_2227 sa_mining_rights_dep_2228 sa_net_oth_intangibl_2229 sa_oth_intangible_as_2230 ///
 sa_oth_intangible_as_2231 sa_oth_intangible_as_2232 sa_oth_intangible_as_2233 sa_oth_intangible_as_2234 sa_oth_intangible_as_2235 sa_intangible_ast_ad_2236 sa_intangible_ast_ad_2237 ///
 sa_intangible_ast_de_2238 sa_intangible_ast_cu_2239 sa_intangible_ast_de_2240 sa_net_intangible_as_2241 sa_land_n_building sa_net_land sa_land sa_land_addn sa_land_addn_reval sa_land_deduct ///
 sa_land_cumm_dep sa_land_dep sa_net_freehold_land_2250 sa_net_leasehold_lan_2251 sa_net_leasehold_imp_2252 sa_gross_leasehold_i_2253 sa_leasehold_imprvmn_2254 sa_leasehold_imprvmn_2255 ///
 sa_leasehold_imprvmn_2256 sa_leasehold_imprvmn_2257 sa_leasehold_imprvmn_2258 sa_net_building sa_building sa_building_addn sa_building_addn_rev_2262 sa_building_deduct ///
 sa_building_cumm_dep_2264 sa_building_dep sa_land_building_add_2266 sa_land_n_building_a_2267 sa_land_n_building_d_2268 sa_land_n_building_c_2269 sa_land_n_building_d_2270 ///
 sa_net_land_n_buildi_2271 sa_plant_mach_comput_2272 sa_net_plant sa_plant sa_plant_addn sa_plant_addn_reval sa_plant_deduct sa_plant_cumm_dep sa_plant_dep sa_net_computer_it ///
 sa_computer_it sa_computer_it_addn sa_computer_it_addn__2283 sa_computer_it_deduc_2284 sa_computer_it_cumm__2285 sa_computer_it_dep sa_net_elec_install__2287 sa_elec_install_fitt_2288 ///
 sa_elec_install_fitt_2289 sa_elec_install_fitt_2290 sa_elec_install_fitt_2291 sa_elec_install_fitt_2292 sa_elec_install_fitt_2293 sa_plant_mach_comput_2294 v295 sa_plant_mach_comput_2296 ///
 sa_plant_mach_comput_2297 sa_plant_mach_comput_2298 sa_net_plant_mach_co_2299 sa_transport_comm_eq_2300 sa_net_transport_inf_2301 sa_transport_infra sa_transport_infra_a_2303 ///
 sa_transport_infra_a_2304 sa_transport_infra_d_2305 sa_transport_infra_c_2306 sa_transport_infra_d_2307 sa_net_transport_veh_2308 sa_transport_vehicle_2309 sa_transport_vehicle_2310 ///
 sa_transport_vehicle_2311 sa_transport_vehicle_2312 sa_transport_vehicle_2313 sa_transport_vehicle_2314 sa_net_comm_equip sa_comm_equip sa_comm_equip_addn sa_comm_equip_addn_r_2318 ///
 sa_comm_equip_deduct_2319 sa_comm_equip_cumm_d_2320 sa_comm_equip_dep sa_transport_comm_eq_2322 v323 sa_transport_comm_eq_2324 sa_transport_comm_eq_2325 v326 sa_net_transport_com_2327 ///
 sa_furn_social_oth_f_2328 sa_net_furn_and_fixt_2329 sa_furn_and_fixtures_2330 sa_furn_and_fixtures_2331 sa_furn_and_fixtures_2332 sa_furn_and_fixtures_2333 sa_furn_and_fixtures_2334 ///
 sa_furn_and_fixtures_2335 sa_net_social_amenit_2336 sa_social_amenities sa_social_amenities__2338 sa_social_amenities__2339 sa_social_amenities__2340 sa_social_amenities__2341 ///
 sa_social_amenities__2342 sa_net_oth_fixed_ast_2343 sa_oth_fixed_ast sa_oth_fixed_ast_add_2345 sa_oth_fixed_ast_add_2346 sa_oth_fixed_ast_ded_2347 sa_oth_fixed_ast_cum_2348 ///
 sa_oth_fixed_ast_dep_2349 sa_furn_social_oth_f_2350 v351 sa_furn_social_oth_f_2352 sa_furn_social_oth_f_2353 sa_furn_social_oth_f_2354 sa_net_furn_social_o_2355 ///
 sa_gross_fixed_ast_a_2356 sa_gross_fixed_ast_a_2357 sa_gross_fixed_ast_d_2358 sa_gross_fixed_ast_c_2359 sa_gross_fixed_ast_d_2360 sa_net_fixed_assets {
	replace `variable' = "" if `variable'== "NA"
	destring `variable', replace
 }


save "$working/prowess_clean", replace
